package com.bw.mapper;

import com.bw.domain.Sugar;
import com.bw.domain.SugarDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarMapper {
    @Select("SELECT sum(amount) value FROM order_info WHERE create_time BETWEEN '2023-01-06 00:00:00' AND '2023-01-06 23:59:59'")
    double getSugarAmount();

    @Select("CALL sp1")
    List<Sugar> getSugarlist1();

    @Select("SELECT o.org_name name,sum(t.actual_distance) num,count(o.org_name) value,SUM(TIMESTAMPDIFF(second,actual_start_time,actual_end_time)) time,SUM(TIMESTAMPDIFF(second,actual_start_time,actual_end_time))/count(o.org_name) avgTime,SUM(t.actual_distance)/count(o.org_name) avgNum from transport_task_detail d,transport_task t,line_base_info i,base_organ o,base_region_info r,base_region_info r1 WHERE d.transport_task_id=t.id and t.line_id=i.id and i.org_id=o.id and o.region_id=r.id and r1.id=r.parent_id and t.actual_distance is not null GROUP BY o.org_name")
    List<SugarDTO> getSugarlist2();

    @Select("SELECT r1.name,count(d.sorter_emp_id) value from transport_task_detail d,transport_task t,line_base_info i,base_organ o,base_region_info r,base_region_info r1 WHERE d.transport_task_id=t.id and t.line_id=i.id and i.org_id=o.id and o.region_id=r.id and r1.id=r.parent_id  GROUP BY r1.name")
    List<Sugar> getSugarlist3();
}
